﻿<cfsilent>
	<cfscript>

		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		propAdvice = getProperty("serviceFactory").getBean("schemaPropertyAdvice");

		grade = event.getArg("Edition");

		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( grade & "版" & "培养方案", "utf-8");
		
		spreadsheetObj = excel.SpreadSheetNew( grade & "版", false);
		excel.SpreadsheetAddrow(spreadsheetObj, "专业/方向,开课学期,课程类别,专业,年级,学位课,是否必修,课程号,课程,学分,任课单位,理论学时,实践学时,上机学时,双学位,辅修");
		
		/* 专业培养方案 */
		sql = "	SELECT
					s.term_index,
					cn.cna_name,
					sbj.sbj_name,
					cp.prop_name,
					c.course_code,
					c.course_name,
					c.course_credit,
					i.institute_name,
					c.period_theory,
					c.period_practice,
					c.period_computer,
					s.shm_prop
				FROM
					t_schema s
					INNER JOIN t_course_nature cn ON cn.cna_id = s.cna_id
					INNER JOIN t_subject sbj ON s.sbj_id = sbj.sbj_id
					INNER JOIN t_course_prop cp ON s.prop_id = cp.prop_id
					INNER JOIN t_course c ON c.cid = s.cid
					INNER JOIN t_institute i ON c.institute_id = i.institute_id
				WHERE
					s.grade = :grade 
				ORDER BY
					sbj.sbj_id,
					s.term_index,
					cn.cna_name,
					cp.prop_name,
					c.course_name";

		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "grade", value = grade, cfsqltype = "cf_sql_char");
		rs_schema = queryObj.execute(sql = sql).getResult();
		

		for(i = 1;i LTE rs_schema.recordCount; i++){
			
			propAdvice.parseProperty( rs_schema['shm_prop'][i] );

			rowData = rs_schema['sbj_name'][i]
						& "," 
						& rs_schema['term_index'][i] 
						& "," 
						& rs_schema['cna_name'][i]
						& "," 
						& rs_schema['sbj_name'][i]
						& "," 
						& grade;
			
			if ( propAdvice.getDegreeCourse() ) {
			
				rowData = rowData 
								& "," 
								& "是";
			}
			else {
				
				rowData = rowData 
								& "," 
								& " ";
				
			}
			
			rowData = rowData 
						& "," 
						& rs_schema['prop_name'][i]
						& ","
						& rs_schema['course_code'][i]
						& ","
						& rs_schema['course_name'][i]
						& ","
						& numberFormat( rs_schema['course_credit'][i], "_.__" )
						& ","
						& rs_schema['institute_name'][i];

			if ( rs_schema['period_theory'][i] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_theory'][i];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_practice'][i] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_practice'][i];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_computer'][i] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_computer'][i];
			}
			else {
				rowData = rowData & ", ";
			}			

			if( propAdvice.getSecondDegree() ){
				
				rowData = rowData 
								& "," 
								& "是";
				
			}
			else {
				
				rowData = rowData 
								& "," 
								& " ";
				
			}
						
			if( propAdvice.getAssistCourse() ){
				
				rowData = rowData 
								& "," 
								& "是";
				
			}
			else {
				
				rowData = rowData 
								& "," 
								& " ";
				
			}

			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>